﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;

namespace lcs.Web.officeOp
{
    public partial class ExcelCheck : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        //导出复写
        public override void VerifyRenderingInServerForm(Control control)       //导出
        { }
        //计划表格导入
        protected void btn_importData_Click(object sender, EventArgs e)
        {
            string filename_export = string.Empty;
            string filename_PC = string.Empty;
            string filename_ID = string.Empty;

            try
            {
                filename_export = UpLoadXls(FileCheck);//上传检验表
                filename_PC = UpLoadXls(FilePC);//上传批次表
                filename_ID = UpLoadXls(FileID);//上传ID表

                DataTable dt_check = ImportXlsToData(filename_export);
                DataTable dt_PC = ImportXlsToData(filename_PC);
                DataTable dt_ID = ImportXlsToData(filename_ID);

                foreach (DataRow row in dt_check.Rows)
                {

                    #region 和批次表对比操作
                    string zbjhbh = row["招标计划编号"].ToString();

                    bool zbjhbh_flag = true;

                    if (string.IsNullOrEmpty(zbjhbh))
                    {
                        zbjhbh_flag = false;
                    }
                    else
                    {
                        for (int i = 0; i < dt_PC.Rows.Count; i++)
                        {
                            if (dt_PC.Rows[i]["批次编号"].ToString() == zbjhbh)
                            {
                                zbjhbh_flag = false;

                                string cgqx = row["采购权限"].ToString();

                                if (cgqx != dt_PC.Rows[i]["采购权限"].ToString())
                                {
                                    row["采购权限"] = cgqx + "|color";
                                }

                                string cgfs = row["采购方式"].ToString();

                                if (cgfs != dt_PC.Rows[i]["采购方式"].ToString())
                                {
                                    row["采购方式"] = cgfs + "|color";
                                }

                                string mlcgfs = row["目录采购方式"].ToString();

                                if (mlcgfs != dt_PC.Rows[i]["目录采购方式"].ToString())
                                {
                                    row["目录采购方式"] = mlcgfs + "|color";
                                }

                                string mlcgms = row["目录采购模式"].ToString();

                                if (mlcgms != dt_PC.Rows[i]["目录采购模式"].ToString())
                                {
                                    row["目录采购模式"] = mlcgms + "|color";
                                }

                                string tptj = dt_PC.Rows[i]["是否同配同价"].ToString();
                                if (tptj == "是")
                                {
                                    row["同配同价"] = "Y";
                                }
                            }
                        }

                       
                    }

                    if (zbjhbh_flag)
                    {
                        row["招标计划编号"] = zbjhbh + "|color";
                    }
                    #endregion


                    #region 和ID表对比操作

                    string wlh = row["物料号"].ToString();

                    for (int i = 0; i < dt_ID.Rows.Count; i++)
                    {
                        if (dt_ID.Rows[i]["物料编码"].ToString() == wlh)
                        {
                            string jsgfID = row["技术规范ID"].ToString();

                            if (jsgfID != dt_ID.Rows[i]["固定ID"].ToString())
                            {
                                row["技术规范ID"] = jsgfID + "|color";
                            }

                            string pgj = row["评估价"].ToString();
                            string gsdj = dt_ID.Rows[i]["估算单价（元）"].ToString();

                            if (!string.IsNullOrEmpty(pgj) && !string.IsNullOrEmpty(gsdj))
                            {
                                double pgj_d = Convert.ToDouble(pgj);
                                double gsdj_d = Convert.ToDouble(gsdj);

                                double gsdj_d_20 = gsdj_d * 1.2;
                                double gsdj_d_20d = gsdj_d * 0.8;

                                if (pgj_d > gsdj_d_20 || pgj_d <gsdj_d_20d)
                                {
                                    row["评估价"] = pgj_d + 0.001;
                                }
                            }

                            string jhdd = row["交货地点及交货方式编号"].ToString();
                            string jhdd_ID = dt_ID.Rows[i]["交货地点及交货方式编号"].ToString();

                            if (!string.IsNullOrEmpty(jhdd))
                            {
                                if (jhdd != jhdd_ID)
                                {
                                    row["交货地点及交货方式编号"] = jhdd + "|color";
                                }
                            }
                            
                        }
                    }

                   
                  

                    #endregion
                }

                if (dt_check != null)
                {
                    this.GridView1.DataSource = dt_check;
                    this.GridView1.DataBind();

                    if (this.GridView1.Rows.Count != 0)
                    {
                        Response.Clear();
                        Response.Buffer = true;
                        Response.Charset = "GB2312";
                        Response.AppendHeader("Content-Disposition", "attachment;filename=checkTable.xls");
                        //Response.ContentEncoding = System.Text.Encoding.UTF7;
                        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                        Response.ContentType = "application/ms-excel";
                        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
                        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
                        //Response.Output.Write("<H4 align='center'>" + filename_export + "</H4>");
                        this.GridView1.RenderControl(oHtmlTextWriter);
                        Response.Output.Write(oStringWriter.ToString());
                        Response.Flush();
                        Response.End();

                    }
                    else
                    {
                        ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('数据为空，无法导出表格')</script>");

                    }
                }

                if (filename_export != string.Empty && System.IO.File.Exists(filename_export))
                {
                    System.IO.File.Delete(filename_export);//删除上传的XLS文件
                }
                if (filename_PC != string.Empty && System.IO.File.Exists(filename_PC))
                {
                    System.IO.File.Delete(filename_PC);//删除上传的XLS文件
                }
                if (filename_ID != string.Empty && System.IO.File.Exists(filename_ID))
                {
                    System.IO.File.Delete(filename_ID);//删除上传的XLS文件
                }

            }
            catch (Exception ex)
            {

            }
        }

        private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
        {
            string orifilename = string.Empty;
            string uploadfilepath = string.Empty;
            string modifyfilename = string.Empty;
            string fileExtend = "";//文件扩展名
            int fileSize = 0;//文件大小
            try
            {
                if (inputfile.Value != string.Empty)
                {
                    //得到文件的大小
                    fileSize = inputfile.PostedFile.ContentLength;
                    if (fileSize == 0)
                    {
                        throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
                    }
                    //得到扩展名
                    fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                    if (fileExtend.ToLower() != "xls")
                    {
                        throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
                    }
                    //路径
                    uploadfilepath = Server.MapPath("~/upload/");
                    //新文件名
                    modifyfilename = System.Guid.NewGuid().ToString();
                    //modifyfilename = inputfile.PostedFile.FileName;
                    modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                    //判断是否有该目录
                    System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
                    if (!dir.Exists)
                    {
                        dir.Create();
                    }
                    if (!uploadfilepath.EndsWith("\\"))
                        orifilename = uploadfilepath + "\\" + modifyfilename;
                    else
                        orifilename = uploadfilepath + modifyfilename;
                    //如果存在,删除文件
                    if (File.Exists(orifilename))
                    {
                        File.Delete(orifilename);
                    }
                    // 上传文件
                    inputfile.PostedFile.SaveAs(orifilename);
                }
                else
                {
                    throw new Exception("请选择要导入的Excel文件!");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return orifilename;
        }


        private DataTable ImportXlsToData(string fileName)
        {
            DataTable dt = null;
            try
            {
                if (fileName == string.Empty)
                {
                    throw new ArgumentNullException("Excel文件上传失败！");
                }
                string oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + "; Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
                OleDbConnection oleDBConn = null;
                OleDbDataAdapter oleAdMaster = null;
                DataTable m_tableName = new DataTable();
                DataSet ds = new DataSet();
                oleDBConn = new OleDbConnection(oleDBConnString);
                oleDBConn.Open();
                m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (m_tableName != null && m_tableName.Rows.Count > 0)
                {
                    m_tableName.TableName = "Sheet1$";
                }
                string sqlMaster;
                sqlMaster = " SELECT *  FROM [" + m_tableName.TableName + "]";
                oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
                oleAdMaster.Fill(ds, "m_tableName");
                oleAdMaster.Dispose();
                // oleDBConn.Close();
                oleDBConn.Dispose();
                //return AddDatasetToSQL(ds);
                dt = ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt;
        }


        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                GridViewRow r = e.Row;

                r.Cells[1].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
                r.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
                r.Cells[6].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
                r.Cells[20].Attributes.Add("style", "vnd.ms-excel.numberformat:@");  

                string cgsqh = r.Cells[6].Text;//采购申请号

                string cgsqh_2 = cgsqh.Substring(0, 2);

                if (cgsqh_2 != "31" && cgsqh_2 != "32" && cgsqh_2 != "33" && cgsqh_2 != "34")
                {
                    r.Cells[6].BackColor = Color.Yellow;
                }

                string dw = r.Cells[29].Text;
                string sqsl = r.Cells[30].Text;

                if (dw == "千米")
                {
                    if (!string.IsNullOrEmpty(sqsl))
                    {
                        decimal sl = Convert.ToDecimal(sqsl);

                        if (sl > 10000)
                        {
                            r.Cells[30].BackColor = Color.Yellow;
                        }

                    }
                }

                if (dw == "套" || dw == "台" || dw == "支" || dw == "个" || dw == "付" || dw == "组")
                {

                    if (!string.IsNullOrEmpty(sqsl))
                    {
                        string [] s = sqsl.Split('.');

                        if (s.Length > 1)
                        {
                            int xsbf = Convert.ToInt32(sqsl.Split('.')[1]);

                            if (xsbf > 0)
                            {
                                r.Cells[30].BackColor = Color.Yellow;
                            }
                        }
                       
                    }
                }

                string cjrq = r.Cells[84].Text;
                string jhrq = r.Cells[39].Text;

                if (!string.IsNullOrEmpty(cjrq) && !string.IsNullOrEmpty(jhrq) && cjrq != "&nbsp;" && jhrq != "&nbsp;")
                {
                    DateTime now = DateTime.Now.Date;//当前日期

                    DateTime cjrq_time = Convert.ToDateTime(cjrq).Date;//创建日期

                    DateTime jhrq_time = Convert.ToDateTime(jhrq).Date;//交货日期

                    TimeSpan cj_ts = jhrq_time - cjrq_time;

                    double cj_day = cj_ts.TotalDays;

                    TimeSpan cn_ts = now - cjrq_time;

                    double cn_day = cn_ts.TotalDays;

                    if (cj_day < 120)
                    {
                        r.Cells[39].BackColor = Color.Yellow;
                    }

                    if (cn_day > 365)
                    {
                        r.Cells[84].BackColor = Color.Yellow;
                    }
                }

                string qwgys = r.Cells[56].Text;

                if (!string.IsNullOrEmpty(qwgys) && qwgys != "&nbsp;")
                {
                    r.Cells[56].BackColor = Color.Yellow;
                }

                string qwgysms = r.Cells[58].Text;

                if (!string.IsNullOrEmpty(qwgysms) && qwgysms != "&nbsp;")
                {
                    r.Cells[58].BackColor = Color.Yellow;
                }

                string cshqgdh = r.Cells[8].Text;//超市化请购单号

                if (!string.IsNullOrEmpty(cshqgdh) && cshqgdh != "&nbsp;")
                {
                    r.Cells[8].BackColor = Color.Yellow;
                }

                string dydj = r.Cells[64].Text;//电压等级

                if (string.IsNullOrEmpty(dydj) || dydj == "&nbsp;" || dydj == "其他")
                {
                    r.Cells[64].BackColor = Color.Yellow;
                }

                string zhjh = r.Cells[66].Text;//综合计划

                if (string.IsNullOrEmpty(zhjh) || zhjh == "&nbsp;" )
                {
                    r.Cells[66].BackColor = Color.Yellow;
                }

                string sfbzwl = r.Cells[72].Text;//是否标准物料

                if (sfbzwl == "0")
                {
                    r.Cells[72].BackColor = Color.Yellow;
                }

                string dtgcms = r.Cells[79].Text;//单体工程描述

                if (dtgcms.Contains("wbs") || dtgcms.Contains("WBS") || dtgcms.Contains("Wbs") || dtgcms.Contains("首成") ||!dtgcms.Contains("聊城"))
                {
                    r.Cells[79].BackColor = Color.Yellow;
                }
               

                #region 对比操作变更颜色“标志为 |color的单元格”
               
                string zbjhbh = r.Cells[10].Text;//招标计划编号

                if (zbjhbh.Split('|').Length > 1)
                {
                    r.Cells[10].Text = zbjhbh.Split('|')[0];
                    r.Cells[10].BackColor = Color.Yellow;
                }

                string cgqx = r.Cells[11].Text;//采购权限

                if (cgqx.Split('|').Length > 1)
                {
                    r.Cells[11].Text = cgqx.Split('|')[0];
                    r.Cells[11].BackColor = Color.Yellow;
                }

                string cgfs = r.Cells[12].Text;//采购方式

                if (cgfs.Split('|').Length > 1)
                {
                    r.Cells[12].Text = cgfs.Split('|')[0];
                    r.Cells[12].BackColor = Color.Yellow;
                }

                string mlcgfs = r.Cells[13].Text;//目录采购方式

                if (mlcgfs.Split('|').Length > 1)
                {
                    r.Cells[13].Text = mlcgfs.Split('|')[0];
                    r.Cells[13].BackColor = Color.Yellow;
                }

                string mlcgms = r.Cells[14].Text;//目录采购模式

                if (mlcgms.Split('|').Length > 1)
                {
                    r.Cells[14].Text = mlcgms.Split('|')[0];
                    r.Cells[14].BackColor = Color.Yellow;
                }

                string jsgfID = r.Cells[15].Text;//技术规范ID

                if (jsgfID.Split('|').Length > 1)
                {
                    r.Cells[15].Text = jsgfID.Split('|')[0];
                    r.Cells[15].BackColor = Color.Yellow;
                }

                string pgj = r.Cells[54].Text;//评估价

                if (pgj.Split('.').Length > 1)
                {
                    if ( pgj.Split('.')[1].Length == 3)
                    {
                        string xs = pgj.Split('.')[0] + "." + pgj.Split('.')[1].Substring(0, 2);

                        r.Cells[54].Text = xs;
                        r.Cells[54].BackColor = Color.Yellow;

                    }
                   
                }

                string jhdd = r.Cells[76].Text;//交货地点及交货方式编号

                if (jhdd.Split('|').Length > 1)
                {
                    r.Cells[76].Text = jhdd.Split('|')[0];
                    r.Cells[76].BackColor = Color.Yellow;
                }

                #endregion

            }
        }


    }
}